﻿using Face8280WebSocketServer.DB;
using Face8280WebSocketServer.DB.Access;
using Face8280WebSocketServer.DB.Access.APIResult;
using Face8280WebSocketServer.DB.Device;
using Face8280WebSocketServer.DB.TimeGroup;
using Face8280WebSocketServer.Interface;
using Face8280WebSocketServer.Model.People;
using Face8280WebSocketServer.Utility.ExpressionExtend;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Threading.Tasks;
using System.Text;
using Face8280WebSocketServer.DTO;
using Face8280WebSocketServer.DB.People;

namespace Face8280WebSocketServer.Service
{
    /// <summary>
    /// 设备门禁权限服务
    /// </summary>
    public class DeviceAccessService : BaseService, IDeviceAccessService
    {
        private ICacheService _Cache;
        private IFaceDriveService _DriveDB;
        private static Dictionary<int, string> PeopleAddErrCodeDict;
        private static HashSet<int> RepeatErrCodes;

        static DeviceAccessService()
        {
            RepeatErrCodes = new HashSet<int>();
            RepeatErrCodes.Add(12);
            //RepeatErrCodes.Add(16); //2021年8月4日 反馈说已废弃这个代码
            //RepeatErrCodes.Add(17); //2021年8月4日 反馈说已废弃这个代码

            PeopleAddErrCodeDict = new Dictionary<int, string>();
            PeopleAddErrCodeDict.Add(0, "导入成功              ");
            PeopleAddErrCodeDict.Add(1, "人脸被拒绝            ");
            PeopleAddErrCodeDict.Add(2, "人员信息数据错误      ");
            PeopleAddErrCodeDict.Add(3, "无法检测脸部          ");
            PeopleAddErrCodeDict.Add(4, "人脸特征值提取失败    ");
            PeopleAddErrCodeDict.Add(5, "检测到多张脸          ");
            PeopleAddErrCodeDict.Add(6, "图像尺寸不符合要求    ");
            PeopleAddErrCodeDict.Add(7, "图像解码失败          ");
            PeopleAddErrCodeDict.Add(8, "图像拷贝出错          ");
            PeopleAddErrCodeDict.Add(9, "没有图像              ");
            PeopleAddErrCodeDict.Add(10, "人员信息写入数据库失败");
            PeopleAddErrCodeDict.Add(11, "图像画质过低          ");
            PeopleAddErrCodeDict.Add(12, "人脸相似度太高        ");
            PeopleAddErrCodeDict.Add(13, "超过一个注册限制      ");
            PeopleAddErrCodeDict.Add(14, "图像格式不符合要求    ");
            PeopleAddErrCodeDict.Add(15, "应用程序内部错误      ");
            //PeopleAddErrCodeDict.Add(16, "重复注册错误          ");//2021年8月4日 反馈说已废弃这个代码
            //PeopleAddErrCodeDict.Add(17, "人脸被人为是别人      ");//2021年8月4日 反馈说已废弃这个代码
            //PeopleAddErrCodeDict.Add(18, "人脸注册校验成功      ");//2021年8月4日 反馈说已废弃这个代码
            //PeopleAddErrCodeDict.Add(19, "人脸注册校验失败      ");//2021年8月4日 反馈说已废弃这个代码
            PeopleAddErrCodeDict.Add(20, "Json 数据解析出错     ");
        }

        public DeviceAccessService(DbContext context, ICacheService cache, IFaceDriveService driveDB) : base(context)
        {
            _Cache = cache;
            _DriveDB = driveDB;
        }

        public async Task Add(List<int> oDeviceIDs, List<int> oPeopleIDs, int iAccessType, FacePeopleAccessDetail detail)
        {

            int EmployeeRoot = detail.EmployeeRoot;
            int TimeGroupID = detail.TimeGroupID;
            DateTime devicePassStart = detail.DevicePassStart;
            DateTime devicePassEnd = detail.DevicePassEnd;
            int devicePassTimeOver = detail.DevicePassTimeOver;
            int devicePassNumber = detail.DevicePassNumber;

            int UploadStatus = 0;//上传状态：0--未上传；1--已上传；2--待删除
            int UploadResult = 0;//上传结果： 0 --无操作；1--正常；>10000 查看协议错误表
            DateTime UploadTime, CreateTime, LastUpdatetime;
            UploadTime = DateTime.Now;
            CreateTime = DateTime.Now;
            LastUpdatetime = DateTime.Now;
            if (iAccessType == 0) UploadStatus = 2;

            #region 方案1 用EFCore 的ORM风格
            /*
            {
                HashSet<int> hsDeviceID = new HashSet<int>(oDeviceIDs);
                HashSet<int> hsPeopleID = new HashSet<int>(oPeopleIDs);

                var Accessdb = Context.Set<FacePeopleAccessDetail>();
                var Peopledb = Context.Set<FacePeople>();
                var Devicedb = Context.Set<FaceDeviceDetail>();

                //首先过滤无效ID
                var DeviceIDFilter = from d in Devicedb where hsDeviceID.Contains(d.ID) select d.ID;
                hsDeviceID = new HashSet<int>(await DeviceIDFilter.ToArrayAsync());

                var PeopleIDFilter = from d in Peopledb where hsPeopleID.Contains(d.ID) select d.ID;
                hsPeopleID = new HashSet<int>(await PeopleIDFilter.ToArrayAsync());

                if (iAccessType == 1)//授权
                {
                    #region 方案1 先查到内存，在分析在用efcore 一条一条插入

                    //查询出所有权限
                    var AccessAll = await (from d in Accessdb
                                           where hsDeviceID.Contains(d.DeviceID) && hsPeopleID.Contains(d.PeopleID)
                                           select new FacePeopleAccessDetail()
                                           {
                                               AccessID = d.AccessID,
                                               DeviceID = d.DeviceID,
                                               PeopleID = d.PeopleID,
                                               EmployeeRoot = d.EmployeeRoot,
                                               TimeGroupID = d.TimeGroupID,
                                               DevicePassTimeOver = d.DevicePassTimeOver,
                                               DevicePassStart = d.DevicePassStart,
                                               DevicePassEnd = d.DevicePassEnd,
                                               DevicePassNumber = d.DevicePassNumber,
                                               UploadStatus = d.UploadStatus,
                                               UploadResult = d.UploadResult
                                           }).AsNoTracking().ToDictionaryAsync(x => $"{x.PeopleID}_{x.DeviceID}");

                    //开始循环
                    foreach (var dDoorID in hsDeviceID)
                    {
                        foreach (var dPeopleID in hsPeopleID)
                        {
                            var sKey = $"{dPeopleID}_{dDoorID}";
                            if (AccessAll.ContainsKey(sKey))
                            {
                                //检查是否需要更新
                                var oldAccess = AccessAll[sKey];
                                var bUpdate = false;

                                if (oldAccess.EmployeeRoot != EmployeeRoot)
                                {
                                    bUpdate = true;
                                }

                                if ((!bUpdate) && oldAccess.TimeGroupID != TimeGroupID)
                                {
                                    bUpdate = true;
                                }
                                if ((!bUpdate) && oldAccess.DevicePassTimeOver != devicePassTimeOver)
                                {
                                    bUpdate = true;
                                }
                                if ((!bUpdate) &&
                                    oldAccess.DevicePassStart.ToString("yyyy-MM-dd HH:mm:ss") != devicePassStart.ToString("yyyy-MM-dd HH:mm:ss"))
                                {
                                    bUpdate = true;
                                }
                                if ((!bUpdate) &&
                                    oldAccess.DevicePassEnd.ToString("yyyy-MM-dd HH:mm:ss") != devicePassEnd.ToString("yyyy-MM-dd HH:mm:ss"))
                                {
                                    bUpdate = true;
                                }
                                if ((!bUpdate) && oldAccess.DevicePassNumber != devicePassNumber)
                                {
                                    bUpdate = true;
                                }
                                if ((!bUpdate) && oldAccess.UploadStatus == 2)
                                {
                                    bUpdate = true;
                                }
                                if (bUpdate)
                                {//更新
                                    oldAccess.EmployeeRoot = EmployeeRoot;
                                    oldAccess.TimeGroupID = TimeGroupID;
                                    oldAccess.DevicePassTimeOver = devicePassTimeOver;
                                    oldAccess.DevicePassStart = devicePassStart;
                                    oldAccess.DevicePassEnd = devicePassEnd;
                                    oldAccess.DevicePassNumber = devicePassNumber;
                                    oldAccess.UploadStatus = 0;
                                    oldAccess.LastUpdatetime = DateTime.Now;
                                    var ent = this.Context.Entry(oldAccess);
                                    ent.Property(p => p.EmployeeRoot).IsModified = true;
                                    ent.Property(p => p.TimeGroupID).IsModified = true;
                                    ent.Property(p => p.DevicePassTimeOver).IsModified = true;
                                    ent.Property(p => p.DevicePassStart).IsModified = true;
                                    ent.Property(p => p.DevicePassEnd).IsModified = true;
                                    ent.Property(p => p.DevicePassNumber).IsModified = true;
                                    ent.Property(p => p.UploadStatus).IsModified = true;
                                    ent.Property(p => p.LastUpdatetime).IsModified = true;
                                }

                            }
                            else
                            {//新增
                                var newAccess = new FacePeopleAccessDetail()
                                {
                                    DeviceID = dDoorID,
                                    PeopleID = dPeopleID,
                                    EmployeeRoot = EmployeeRoot,
                                    TimeGroupID = TimeGroupID,
                                    DevicePassTimeOver = devicePassTimeOver,
                                    DevicePassStart = devicePassStart,
                                    DevicePassEnd = devicePassEnd,
                                    DevicePassNumber = devicePassNumber,
                                    UploadStatus = 0,
                                    LastUpdatetime = DateTime.Now
                                };
                                Accessdb.Add(newAccess);
                            }
                        }
                    }
                    #endregion


                }
                else//删除权限
                {
                    #region 方案1 先查找已存在，然后再分析状态，在一条一条更新
                    
                    //查询出所有权限
                    var AccessAll = from d in Accessdb
                                    where hsDeviceID.Contains(d.DeviceID) && hsPeopleID.Contains(d.PeopleID)
                                    select new FacePeopleAccessDetail()
                                    {
                                        AccessID = d.AccessID,
                                        UploadStatus = d.UploadStatus
                                    };
                    AccessAll = AccessAll.AsTracking();
                    foreach (var item in AccessAll)
                    {
                        if (item.UploadStatus != 2)
                        {
                            item.UploadStatus = 2;
                            item.UploadTime = DateTime.Now;
                            var ent = this.Context.Entry(item);
                            ent.Property(p => p.UploadStatus).IsModified = true;
                            ent.Property(p => p.UploadTime).IsModified = true;
                        }


                    }
                    
                    #endregion


                }
                this.Commit();
            }
    */
            #endregion

            #region 方案2 直接写SQL语句 临时表+批量操作
            {
                var sSQLBuf = new StringBuilder(10240);

                var sDeviceIDTableName = $"[AccessDeviceID_{DateTime.Now:yyyyMMddHHmmssffff}]";
                var sPeopleIDTableName = $"[AccessPeopleID_{DateTime.Now:yyyyMMddHHmmssffff}]";

                #region 第一步 创建2个临时表 AccessDeviceID 和 AccessPeopleID
                //第一步 创建2个临时表，AccessDeviceID 和 AccessPeopleID

                sSQLBuf.AppendLine(@$"
                CREATE TABLE {sDeviceIDTableName} (
                    DeviceID INTEGER PRIMARY KEY
                );
                ");


                sSQLBuf.AppendLine(@$" 

                CREATE TABLE {sPeopleIDTableName} (
                    PeopleID INTEGER PRIMARY KEY
                );
                ");
                //创建导入语句
                //导入设备ID
                sSQLBuf.AppendLine($"Insert Into {sDeviceIDTableName} (DeviceID) values ");
                foreach (var item in oDeviceIDs)
                {
                    sSQLBuf.Append("(").Append(item).Append("),");
                }
                sSQLBuf.Length--;
                sSQLBuf.AppendLine(";");
                //导入人员ID
                sSQLBuf.AppendLine($"Insert Into {sPeopleIDTableName} (PeopleID) values ");
                foreach (var item in oPeopleIDs)
                {
                    sSQLBuf.Append("(").Append(item).Append("),");
                }
                sSQLBuf.Length--;
                sSQLBuf.AppendLine(";");
                //Excute(sSQLBuf.ToString());
                //sSQLBuf.Clear();
                //过滤无效数据
                sSQLBuf.AppendLine(@$"DELETE FROM {sDeviceIDTableName} WHERE [DeviceID] IN
(SELECT td.[DeviceID] FROM {sDeviceIDTableName} td LEFT JOIN [FaceDeviceInfo] d ON td.[DeviceID] = d.[ID] WHERE d.ID IS NULL); ");
                //Excute(sSQLBuf.ToString());
                //sSQLBuf.Clear();

                sSQLBuf.AppendLine(@$"DELETE FROM {sPeopleIDTableName} WHERE [PeopleID] IN
(SELECT tp.[PeopleID] FROM {sPeopleIDTableName} tp LEFT JOIN [FacePeopleInfo] p ON tp.[PeopleID] = p.[ID] WHERE p.[ID] IS NULL); ");
                //Excute(sSQLBuf.ToString());
                //sSQLBuf.Clear();
                //删除权限表中已存在的权限
                sSQLBuf.AppendLine(@$"Delete from [FacePeopleAccessDetail] where 
                DeviceID in (Select [DeviceID] from {sDeviceIDTableName}) And 
                PeopleID in (Select [PeopleID] from {sPeopleIDTableName});");

                //提交数据库

                Excute(sSQLBuf.ToString());
                #endregion

                #region 第二步，导入人员权限

                sSQLBuf.Clear();
                sSQLBuf.AppendLine(@"insert into [FacePeopleAccessDetail] (
                PeopleID,DeviceID,
                TimeGroupID,EmployeeRoot,DevicePassStart,
                DevicePassEnd,DevicePassTimeOver,DevicePassNumber,
                UploadStatus,UploadResult,UploadTime,
                CreateTime,LastUpdatetime,RepeatID) 
                select p.[PeopleID],d.[DeviceID],
                {0},{1},{2},
                {3},{4},{5},
                {6},{7},{8},
                {9},{10},0 from");
                sSQLBuf.AppendLine(@$"
                (select tp.[PeopleID] from [FacePeopleInfo] as p inner join {sPeopleIDTableName} tp on p.[ID] = tp.[PeopleID]) as p,
                (select td.[DeviceID] from [FaceDeviceInfo] as d inner join {sDeviceIDTableName} td on d.[ID] = td.[DeviceID]) as d 
                ");

                Excute(sSQLBuf.ToString(),
                    TimeGroupID, EmployeeRoot, devicePassStart,
                    devicePassEnd, devicePassTimeOver, devicePassNumber,
                    UploadStatus, UploadResult, UploadTime,
                    CreateTime, LastUpdatetime);

                #endregion

                #region 第三步 删除临时表
                //删除临时表

                sSQLBuf.Clear();

                sSQLBuf.AppendLine($"Drop Table {sPeopleIDTableName};");
                sSQLBuf.AppendLine($"Drop Table {sDeviceIDTableName};");
                Excute(sSQLBuf.ToString());
                #endregion



            }
            #endregion
            UpdateDeviceAccessTotal1(oDeviceIDs);
        }

        public void Add(FacePeopleAccessDetail pAccess)
        {
            var access = Set<FacePeopleAccessDetail>().FirstOrDefault(a => a.PeopleID == pAccess.PeopleID && a.DeviceID == pAccess.DeviceID);
            if (access == null)
            {
                Insert(pAccess);
            }
            else
            {
                access.EmployeeRoot = pAccess.EmployeeRoot;
                access.TimeGroupID = pAccess.TimeGroupID;
                access.DevicePassStart = pAccess.DevicePassStart;
                access.DevicePassEnd = pAccess.DevicePassEnd;
                access.DevicePassNumber = pAccess.DevicePassNumber;
                access.DevicePassTimeOver = pAccess.DevicePassTimeOver;
                access.UploadStatus = 1;
                access.UploadResult = 1;
                access.RepeatID = 0;
                access.LastUpdatetime = DateTime.Now;
                Commit();
            }
        }
        private void UpdateDeviceAccessTotal1(IEnumerable<int> hsDeviceID)
        {
            var deviceList = _Cache.GetDevices();
            var access = Set<FacePeopleAccessDetail>().Where(a => a.UploadStatus != 1).ToList();
            var peopleId = access.Select(a => a.PeopleID).Distinct().ToArray();
            var peopleList = Set<FacePeopleInfo>().Where(a => peopleId.Contains(a.Id)).ToDictionary(a => a.Id);
            var list = deviceList.Where(a => hsDeviceID.Contains(a.Id)).ToList();
            _Cache.UpdateDeviceAccessTotal1(peopleList, list, access);


        }
        /// <summary>
        /// 更新设备权限统计数
        /// </summary>
        /// <param name="hsDeviceID"></param>
        public async Task UpdateDeviceAccessTotal(IEnumerable<int> hsDeviceID)
        {
            var ret = (NewPeople: 0, DelPeople: 0, RemoteCount: 0, AccessTotal: 0);
            //加载设备权限统计数
            var newAccessQuery = await (from device in Set<FaceDeviceDetail>()
                                        join dAccess in Set<FacePeopleAccessDetail>()
                                        on device.ID equals dAccess.DeviceID
                                        where dAccess.UploadStatus == 0 && hsDeviceID.Contains(device.ID)
                                        group device by device.DeviceID into groupedItems
                                        select new { SN = groupedItems.Key, Count = groupedItems.Count() }).ToDictionaryAsync(x => x.SN);
            ;


            //查询待删除
            var deleteAccessQuery = await (from device in Set<FaceDeviceDetail>()
                                           join dAccess in Set<FacePeopleAccessDetail>()
                                           on device.ID equals dAccess.DeviceID
                                           where dAccess.UploadStatus == 2 && hsDeviceID.Contains(device.ID)
                                           group device by device.DeviceID into groupedItems
                                           select new { SN = groupedItems.Key, Count = groupedItems.Count() }).ToDictionaryAsync(x => x.SN);
            ;

            //查询所有
            var allAccessQuery = await (from device in Set<FaceDeviceDetail>()
                                        join dAccess in Set<FacePeopleAccessDetail>()
                                        on device.ID equals dAccess.DeviceID
                                        where dAccess.UploadStatus < 2 && hsDeviceID.Contains(device.ID)
                                        group device by device.DeviceID into groupedItems
                                        select new { SN = groupedItems.Key, Count = groupedItems.Count() }).ToDictionaryAsync(x => x.SN);

            //查询所有门的SN
            var allDeviceQuery = await (from device in Set<FaceDeviceDetail>()
                                        where hsDeviceID.Contains(device.ID)
                                        select device.DeviceID).ToDictionaryAsync(x => x);

            var dic = new Dictionary<string, (int NewPeople, int DelPeople, int AccessTotal)>();
            foreach (var item in allDeviceQuery)
            {
                var sn = item.Key;

                var total = (NewPeople: 0, DelPeople: 0, AccessTotal: 0);
                if (newAccessQuery.ContainsKey(sn))
                {
                    total.NewPeople = newAccessQuery[sn].Count;
                }
                if (deleteAccessQuery.ContainsKey(sn))
                {
                    total.DelPeople = deleteAccessQuery[sn].Count;
                }
                if (allAccessQuery.ContainsKey(sn))
                {
                    total.AccessTotal = allAccessQuery[sn].Count;
                }
                dic.Add(sn, total);
            }
            allDeviceQuery = null;
            allAccessQuery = null;
            deleteAccessQuery = null;
            newAccessQuery = null;

            _Cache.UpdateDeviceAccessCache(dic);
        }


        public void ClearAccess()
        {
            Excute("Delete from FacePeopleAccessDetail");
            AddUserLog("权限管理", "清空所有人员开门权限");
            Commit();

            var query = from device in Set<FaceDeviceDetail>()
                        select device.DeviceID;
            var total = (NewPeople: 0, DelPeople: 0, AccessTotal: 0);
            var dic = new Dictionary<string, (int NewPeople, int DelPeople, int AccessTotal)>();
            foreach (var item in query)
            {
                dic.Add(item, total);
            }


            _Cache.UpdateDeviceAccessCache(dic);

            var dicEmpty = new Dictionary<string, int>();
            foreach (var item in query)
            {
                dicEmpty.Add(item, 1);
            }
            query = null;
            _Cache.UpdateDeviceEmptyPeopleCache(dicEmpty);
            NotificationDelete(null, true);
        }

        public void Delete(List<int> iAccessID)
        {
            HashSet<int> hsAccessID = new HashSet<int>(iAccessID);
            var Accessdb = Set<FacePeopleAccessDetail>();
            var oldAccess = Accessdb.Where(x => hsAccessID.Contains(x.AccessID)).Select(x => new FacePeopleAccessDetail()
            {
                AccessID = x.AccessID,
                UploadStatus = x.UploadStatus
            });
            if (oldAccess == null)
                return;

            var bUpdate = false;
            var iUpdateCount = 0;
            foreach (var item in oldAccess)
            {
                if (item.UploadStatus != 2)
                {
                    bUpdate = true;
                }
                if (bUpdate)
                {//更新
                    iUpdateCount++;
                    item.UploadStatus = 2;
                    item.LastUpdatetime = DateTime.Now;
                    this.Context.Attach(item);
                    var ent = this.Context.Entry(item);
                    ent.Property(p => p.UploadStatus).IsModified = true;
                    ent.Property(p => p.LastUpdatetime).IsModified = true;
                }
            }
            Commit();
            NotificationDelete(hsAccessID);
            //if (iUpdateCount > 0)
            //{
            //    //更新缓存
            //    //查询出所有权限
            //    var Devices = Accessdb.Where(x => hsAccessID.Contains(x.AccessID)).Select(x => x.DeviceID).Distinct();
            //    var hsDeviceID = new HashSet<int>(Devices);

            //    UpdateDeviceAccessTotal(hsDeviceID).Wait();
            //}

        }
        /// <summary>
        /// 通知删除
        /// </summary>
        /// <param name="hsAccessID"></param>
        private void NotificationDelete(HashSet<int> hsAccessID, bool deleteAll = false)
        {

            var Devices = _Cache.GetDevices();
            if (deleteAll == true)
            {
                Devices.ForEach(a => a.DeleteAllPeople = true);
                return;
            }
            var accessList = Set<FacePeopleAccessDetail>().Where(x => hsAccessID.Contains(x.AccessID)).ToList();
            var pidList = accessList.Select(a => a.PeopleID).Distinct().ToArray();
            var peopleIdList = Set<FacePeopleInfo>().Where(a => pidList.Contains(a.Id)).ToDictionary(a => a.Id);
            foreach (var device in Devices)
            {
                foreach (var item in accessList)
                {
                    if (device.Id == item.DeviceID)
                    {
                        device.DeletePeople.Add(peopleIdList[item.PeopleID].Code);
                    }
                }
            }
        }

        public DeviceAccessDetail_DTO GetAccessDetail(int iAccessID)
        {
            var Accessdb = Context.Set<FacePeopleAccessDetail>();
            var Peopledb = Context.Set<FacePeopleInfo>();
            var Devicedb = Context.Set<FaceDeviceInfo>();
            var lst = from paccess in Accessdb
                      join people in Peopledb
                      on paccess.PeopleID equals people.Id
                      join device in Devicedb
                      on paccess.DeviceID equals device.Id
                      where paccess.AccessID == iAccessID
                      select new DeviceAccessDetail_DTO()
                      {
                          AccessID = paccess.AccessID,
                          EmployeeRoot = paccess.EmployeeRoot,
                          TimeGroupID = paccess.TimeGroupID,
                          DevicePassStart = paccess.DevicePassStart,
                          DevicePassEnd = paccess.DevicePassEnd,
                          DevicePassTimeOver = paccess.DevicePassTimeOver,
                          DevicePassNumber = paccess.DevicePassNumber,
                          UploadStatus = paccess.UploadStatus,
                          UploadResult = paccess.UploadResult,
                          RepeatID = paccess.RepeatID,
                          UploadTime = paccess.UploadTime,
                          CreateTime = paccess.CreateTime,
                          LastUpdatetime = paccess.LastUpdatetime,
                          DeviceID = device.DeviceSn,
                          DeviceName = device.DeviceName,
                          EmployeeID = people.Code,
                          EmployeeName = people.Name,
                          EmployeePassword = people.Pin,
                          EmployeePhoto = people.Photo,
                          EmployeeIC = people.Card,
                          EmployeeJob = people.Job,
                          EmployeePhone = people.Phone,
                      };
            return lst.FirstOrDefault();
        }

        public PageResult<DeviceAccessDetail_DTO_Mini> GetList(DeviceAccessDetail_DTO queryPar, BasePageParameter pagePar)
        {
            var Accessdb = Context.Set<FacePeopleAccessDetail>();
            var Peopledb = Context.Set<FacePeopleInfo>();
            var Devicedb = Context.Set<FaceDeviceInfo>();
            var lst = from paccess in Accessdb
                      join people in Peopledb
                      on paccess.PeopleID equals people.Id
                      join device in Devicedb
                      on paccess.DeviceID equals device.Id
                      select new DeviceAccessDetail_DTO_Mini()
                      {
                          AccessID = paccess.AccessID,
                          EmployeeRoot = paccess.EmployeeRoot,
                          TimeGroupID = paccess.TimeGroupID,
                          DevicePassEnd = paccess.DevicePassEnd,
                          DevicePassNumber = paccess.DevicePassNumber,
                          UploadStatus = paccess.UploadStatus,
                          UploadResult = paccess.UploadResult,
                          UploadTime = paccess.UploadTime,
                          DeviceID = device.DeviceSn,
                          DeviceName = device.DeviceName,
                          EmployeeID = people.Code,
                          EmployeeName = people.Name,
                          EmployeePassword = people.Pin,
                          EmployeePhoto = people.Photo,
                          EmployeeIC = people.Card,
                      };
            lst = lst.AsNoTracking();
            if (queryPar.EmployeeID > 0) lst = lst.Where(x => x.EmployeeID == queryPar.EmployeeID);
            if (!string.IsNullOrWhiteSpace(queryPar.DeviceID)) lst = lst.Where(x => x.DeviceID == queryPar.DeviceID);
            if (!string.IsNullOrWhiteSpace(queryPar.DeviceName)) lst = lst.Where(x => x.DeviceName == queryPar.DeviceName);
            if (!string.IsNullOrWhiteSpace(queryPar.EmployeeName)) lst = lst.Where(x => x.EmployeeName.Contains(queryPar.EmployeeName));
            //if (string.IsNullOrWhiteSpace(queryPar.EmployeeJob)) lst.Where(x => x.EmployeeJob.Contains(queryPar.EmployeeJob));
            if (queryPar.EmployeeIC > 0) lst = lst.Where(x => x.EmployeeIC == queryPar.EmployeeIC);
            //if (string.IsNullOrWhiteSpace(queryPar.EmployeePhone)) lst.Where(x => x.EmployeePhone.Contains(queryPar.EmployeePhone));
            if (queryPar.EmployeeRoot != 255) lst = lst.Where(x => x.EmployeeRoot == queryPar.EmployeeRoot);
            if (queryPar.UploadStatus != 255) lst = lst.Where(x => x.UploadStatus == queryPar.UploadStatus);
            if (queryPar.UploadResult != 255)
            {
                if (queryPar.UploadResult == 254)//查找异常项
                {
                    lst = lst.Where(x => x.UploadResult > 1);
                }
                else
                {
                    lst = lst.Where(x => x.UploadResult == queryPar.UploadResult);
                }

            }



            PageResult<DeviceAccessDetail_DTO_Mini> result = new PageResult<DeviceAccessDetail_DTO_Mini>()
            {
                DataList = lst.Skip((pagePar.pageIndex - 1) * pagePar.pageSize).Take(pagePar.pageSize).ToList(),
                PageIndex = pagePar.pageIndex,
                PageSize = pagePar.pageSize,
                TotalCount = lst.Count()
            };

            return result;

        }

        public async Task Update(FacePeopleAccessDetail detail)
        {
            var Accessdb = Set<FacePeopleAccessDetail>();
            var oldAccess = Accessdb.Where(x => x.AccessID == detail.AccessID).AsNoTracking().FirstOrDefault();
            if (oldAccess == null)
                return;

            var bUpdate = false;

            if (oldAccess.EmployeeRoot != detail.EmployeeRoot)
            {
                bUpdate = true;
            }

            if ((!bUpdate) && oldAccess.TimeGroupID != detail.TimeGroupID)
            {
                bUpdate = true;
            }
            if ((!bUpdate) && oldAccess.DevicePassTimeOver != detail.DevicePassTimeOver)
            {
                bUpdate = true;
            }
            if ((!bUpdate) &&
                oldAccess.DevicePassStart.ToString("yyyy-MM-dd HH:mm:ss") != detail.DevicePassStart.ToString("yyyy-MM-dd HH:mm:ss"))
            {
                bUpdate = true;
            }
            if ((!bUpdate) &&
                oldAccess.DevicePassEnd.ToString("yyyy-MM-dd HH:mm:ss") != detail.DevicePassEnd.ToString("yyyy-MM-dd HH:mm:ss"))
            {
                bUpdate = true;
            }
            if ((!bUpdate) && oldAccess.DevicePassNumber != detail.DevicePassNumber)
            {
                bUpdate = true;
            }
            if ((!bUpdate) && oldAccess.UploadStatus == 2)
            {
                bUpdate = true;
            }
            if (bUpdate)
            {//更新
                oldAccess.EmployeeRoot = detail.EmployeeRoot;
                oldAccess.TimeGroupID = detail.TimeGroupID;
                oldAccess.DevicePassTimeOver = detail.DevicePassTimeOver;
                oldAccess.DevicePassStart = detail.DevicePassStart;
                oldAccess.DevicePassEnd = detail.DevicePassEnd;
                oldAccess.DevicePassNumber = detail.DevicePassNumber;
                oldAccess.UploadStatus = 0;
                oldAccess.UploadResult = 0;
                oldAccess.LastUpdatetime = DateTime.Now;
                var ent = this.Context.Entry(oldAccess);
                ent.Property(p => p.EmployeeRoot).IsModified = true;
                ent.Property(p => p.TimeGroupID).IsModified = true;
                ent.Property(p => p.DevicePassTimeOver).IsModified = true;
                ent.Property(p => p.DevicePassStart).IsModified = true;
                ent.Property(p => p.DevicePassEnd).IsModified = true;
                ent.Property(p => p.DevicePassNumber).IsModified = true;
                ent.Property(p => p.UploadStatus).IsModified = true;
                ent.Property(p => p.LastUpdatetime).IsModified = true;

                Commit();


                //更新缓存
                //查询出所有权限

                var hsDeviceID = new HashSet<int>();
                hsDeviceID.Add(oldAccess.DeviceID);

               await UpdateDeviceAccessTotal(hsDeviceID);


            }

        }

        public List<APIResult_DevicePeopleModel> GetSyncAccessbyDoorID(string SN, int doorID, int iLimit)
        {
            var Accessdb = Context.Set<FacePeopleAccessDetail>();
            var Peopledb = Context.Set<FacePeople>();
            var lst = from paccess in Accessdb
                      join people in Peopledb
                      on paccess.PeopleID equals people.ID
                      where paccess.DeviceID == doorID && paccess.UploadStatus == 0
                      select new APIResult_DevicePeopleModel()
                      {
                          //AccessID = paccess.AccessID,

                          EmployeeID = people.EmployeeID,
                          EmployeeName = people.EmployeeName,
                          EmployeeJob = people.EmployeeJob,
                          EmployeePhone = people.EmployeePhone,
                          EmployeeIdentity = people.EmployeeIdentity,
                          EmployeeAddress = people.EmployeeAddress,

                          EmployeePassword = people.EmployeePassword,
                          EmployeeIc = people.EmployeeIC,
                          EmployeePhotoWay = people.EmployeePhotoWay,
                          EmployeePhoto = people.EmployeePhoto,
                          EmployeeShold = people.EmployeeShold,

                          EmployeeRoot = paccess.EmployeeRoot,
                          TimegroupID = paccess.TimeGroupID,
                          DevicePassBean = new APIResult_FacePeopleAccessDetail()
                          {
                              DevicePassStart = paccess.DevicePassStart,
                              DevicePassEnd = paccess.DevicePassEnd,
                              DevicePassTimeOver = paccess.DevicePassTimeOver,
                              DevicePassNumber = paccess.DevicePassNumber,
                          },



                      };
            lst = lst.AsNoTracking().Take(iLimit);

            var peoples = lst.ToList();
            foreach (var item in peoples)
            {
                AddUserLog("权限管理", $"设备:{SN} 拉取人员:{item.EmployeeName}({item.EmployeeID})");
            }
            Commit();
            return peoples;
        }

        public void SavePeopleAccessUploadResult(string sSN, int failNumber, List<APIRequest_PeopleInfo> peopleInfos)
        {
            //首先从缓存中拿取设备已拉取的人员ID
            //var cache = _Cache.GetDeviceReadPeopleCodeList(sSN);
            //if (cache.Codes == null || cache.DoorID == 0)
            //{
            //    return;
            //}
            Dictionary<long, APIRequest_PeopleInfo> failPeopleDict = new Dictionary<long, APIRequest_PeopleInfo>();
            if (failNumber > 0 && peopleInfos != null)
            {
                foreach (var people in peopleInfos)
                {
                    failPeopleDict.Add(people.EmployeeID, people);
                }
                //cache.Codes.RemoveAll(x => peopleInfos.Find(p => p.EmployeeID == x) != null);
            }
            var device = Set<FaceDeviceInfo>().FirstOrDefault(a => a.DeviceSn.Equals(sSN));
            var Codes = peopleInfos.Select(a => a.EmployeeID).ToArray();
            var Accessdb = Context.Set<FacePeopleAccessDetail>();
            var Peopledb = Context.Set<FacePeopleInfo>();
            if (peopleInfos.Count > 0)
            {
                var lst = from paccess in Accessdb
                          join people in Peopledb
                          on paccess.PeopleID equals people.Id
                          where paccess.DeviceID == device.Id &&
                          Codes.Contains(people.Code)
                          select new
                          {
                              paccess.AccessID,
                              people.Code,
                              people.Name
                          };


                foreach (var item in lst)
                {
                    var accessDetail = new FacePeopleAccessDetail();
                    accessDetail.AccessID = item.AccessID;
                    accessDetail.UploadStatus = 1;
                    accessDetail.UploadResult = 1;
                    accessDetail.RepeatID = 0;
                    accessDetail.UploadTime = DateTime.Now;
                    var ent = this.Context.Entry(accessDetail);
                    ent.Property(p => p.UploadStatus).IsModified = true;
                    ent.Property(p => p.UploadTime).IsModified = true;
                    ent.Property(p => p.UploadResult).IsModified = true;
                    ent.Property(p => p.RepeatID).IsModified = true;

                    if (failPeopleDict.ContainsKey(item.Code))
                    {
                        var errInfo = failPeopleDict[item.Code];
                        if (PeopleAddErrCodeDict.ContainsKey(errInfo.ErrCode))
                        {
                            string sTip = $"设备:{sSN} 拉取人员:{item.Name}({item.Code}) ，存储失败，失败代码：{errInfo.ErrCode}({PeopleAddErrCodeDict[errInfo.ErrCode]})";
                            if (RepeatErrCodes.Contains(errInfo.ErrCode))
                            {
                                sTip = $"{sTip} ，重复人员编号：{errInfo.RepeatID}";
                                //人员照片重复
                                accessDetail.RepeatID = errInfo.RepeatID;
                            }

                            AddUserLog("权限管理", sTip);
                        }
                        else
                        {
                            AddUserLog("权限管理", $"设备:{sSN} 拉取人员:{item.Name}({item.Code}) ，存储失败，失败代码：{errInfo.ErrCode}(未定义),RepeatID:{errInfo.RepeatID}");
                        }

                        accessDetail.UploadResult = 10000 + errInfo.ErrCode;
                    }
                    else
                    {
                        AddUserLog("权限管理", $"设备:{sSN} 拉取人员:{item.Name}({item.Code}) ，存储成功");
                    }
                }
                Commit();

                //更新全新统计数
                //   UpdateDeviceAccessTotal(new int[] { cache.DoorID }).Wait();
            }

            _Cache.DeleteDeviceReadPeopleCodeList(sSN);
        }


        public void SavePeopleAccessUploadResult(string sSn, uint code, long repeatCode, int error)
        {
            var device = Set<FaceDeviceInfo>().FirstOrDefault(a => a.DeviceSn.Equals(sSn));
            var people = Set<FacePeopleInfo>().FirstOrDefault(a => a.Code.Equals(code));
            var access = Set<FacePeopleAccessDetail>().FirstOrDefault(a => a.DeviceID == device.Id && a.PeopleID == people.Id);
            access.UploadStatus = 1;
            access.UploadResult = 1;
            access.RepeatID = 0;
            access.UploadTime = DateTime.Now;
            access.UploadResult = error;
            access.RepeatID = repeatCode;
            string sTip = $"设备:{sSn} 下发人员:{people.Name}({people.Code})";
            if (access.RepeatID != 0 || error != 200)
            {
                sTip = $"{sTip} ，重复人员编号：{access.RepeatID} 存储失败，失败代码：{error}";
            }
            else
            {
                sTip += "存储成功";
            }
            AddUserLog("权限管理", sTip);
            Commit();
        }
        public List<APIResult_PeopleID> GetDeleteAccessbyDoorID(string SN, int DoorID, int limit)
        {
            var Accessdb = Context.Set<FacePeopleAccessDetail>();
            var Peopledb = Context.Set<FacePeople>();
            var lst = from paccess in Accessdb
                      join people in Peopledb
                      on paccess.PeopleID equals people.ID
                      where paccess.DeviceID == DoorID && paccess.UploadStatus == 2
                      select new
                      {
                          EmployeeID = people.EmployeeID,
                          EmployeeName = people.EmployeeName,
                      };
            lst = lst.AsNoTracking().Take(limit);

            var peoples = lst.ToList();
            foreach (var item in peoples)
            {
                AddUserLog("权限管理", $"设备:{SN} 拉取待删除人员:{item.EmployeeName}({item.EmployeeID})");
            }
            Commit();
            return peoples.Select(x => new APIResult_PeopleID() { EmployeeID = x.EmployeeID }).ToList();
        }

        public async Task SaveDeleteAccessUploadResult(string SN, int deleteAll)
        {

            if (deleteAll == 1)
            {
                var device = _Cache.GetDevice(SN);
                if (device == null) return;

                //删除了所有人员
                Excute("Delete from FacePeopleAccessDetail where DeviceID={0} and UploadStatus=2 ", device.Id);
                //更新已上传人员为待上传人员
                Excute("Update FacePeopleAccessDetail set  UploadStatus=0,UploadResult=0  where DeviceID={0} and UploadStatus=1  ", device.Id);

                Excute("Update RemoteTask set  TaskStatus={0},TaskRunTime={1}  where DeviceID={2} and TaskStatus=0 and TaskType = 5  ", 1, DateTime.Now, device.Id);

                AddUserLog("权限管理", $"设备:{SN} 清空所有人员命令执行完毕");

                _Cache.UpdateDeviceAccessCache(SN, (x) =>
                {
                    x.Add("EmptyPeople", "0");
                    x.Add("DeleteTotal", "0");

                    //加载设备权限统计数
                    var iAccessCount = Query<FacePeopleAccessDetail>(x => x.DeviceID == device.Id).Count();
                    x.Add("AccessTotal", iAccessCount.ToString());
                    x.Add("NewTotal", iAccessCount.ToString());
                });
                return;
            }

            //首先从缓存中拿取设备已拉取的人员ID
            var cache = _Cache.GetDeviceDeletePeopleCodeList(SN);
            if (cache.Codes == null || cache.DoorID == 0)
            {
                return;
            }

            var Accessdb = Context.Set<FacePeopleAccessDetail>();
            var Peopledb = Context.Set<FacePeopleInfo>();
            if (cache.Codes.Count > 0)
            {
                var lst = from paccess in Accessdb
                          join people in Peopledb
                          on paccess.PeopleID equals people.Id
                          where paccess.DeviceID == cache.DoorID &&
                          cache.Codes.Contains(people.Code)
                          select new
                          {
                              paccess.AccessID,
                              paccess.UploadStatus,
                              people.Code,
                              people.Name
                          };


                foreach (var item in lst)
                {
                    var accessDetail = new FacePeopleAccessDetail();
                    accessDetail.AccessID = item.AccessID;
                    accessDetail.UploadStatus = 0;
                    if (item.UploadStatus == 2)
                    {
                        Accessdb.Remove(accessDetail);
                    }
                    else if (item.UploadStatus == 1)
                    {
                        var ent = this.Context.Entry(accessDetail);
                        ent.Property(p => p.UploadStatus).IsModified = true;
                    }

                    AddUserLog("权限管理", $"设备:{SN} 删除人员:{item.Name}({item.Code}) 成功");
                }
                Commit();

                //更新全新统计数
              await  UpdateDeviceAccessTotal(new int[] { cache.DoorID });

            }

            _Cache.DeleteDeviceDeletePeopleCodeList(SN);
        }


        public async Task Add_ALL(List<int> oDeviceIDs, int iAccessType, FacePeopleAccessDetail detail)
        {
            //iAccessType 1--授权；0--删除权限
            int EmployeeRoot = detail.EmployeeRoot;
            int TimeGroupID = detail.TimeGroupID;
            DateTime devicePassStart = detail.DevicePassStart;
            DateTime devicePassEnd = detail.DevicePassEnd;
            int devicePassTimeOver = detail.DevicePassTimeOver;
            int devicePassNumber = detail.DevicePassNumber;
            int UploadStatus = 0;//上传状态：0--未上传；1--已上传；2--待删除
            int UploadResult = 0;//上传结果： 0 --无操作；1--正常；>10000 查看协议错误表
            DateTime UploadTime, CreateTime, LastUpdatetime;
            UploadTime = DateTime.Now;
            CreateTime = DateTime.Now;
            LastUpdatetime = DateTime.Now;
            if (iAccessType == 0) UploadStatus = 2;
            HashSet<int> hsDeviceID = new HashSet<int>(oDeviceIDs);
            string sDoorIDList = string.Join(",", hsDeviceID.ToArray());
            ////创建临时表
            //string sTableName = $"[FacePeopleAccessDetail_{DateTime.Now:yyyyMMddHHmmssfff}]";
            //string sSQL = $"create table {sTableName} as select * from FacePeopleAccessDetail where 1=0;";
            //Excute(sSQL);

            //删除门的所有记录
            string sSQL = $"Delete from [FacePeopleAccessDetail] where DeviceID in ({sDoorIDList})";
            Excute(sSQL);

            sSQL = @"insert into [FacePeopleAccessDetail] (
                PeopleID,DeviceID,
                TimeGroupID,EmployeeRoot,DevicePassStart,
                DevicePassEnd,DevicePassTimeOver,DevicePassNumber,
                UploadStatus,UploadResult,UploadTime,
                CreateTime,LastUpdatetime,RepeatID) 
                select p.[ID],d.[ID],
                {0},{1},{2},
                {3},{4},{5},
                {6},{7},{8},
                {9},{10},0 from
                [FacePeople] as p,[FaceDevice] as d ";
            sSQL = $" {sSQL} where d.id in ({sDoorIDList});";
            Excute(sSQL,
                TimeGroupID, EmployeeRoot, devicePassStart,
                devicePassEnd, devicePassTimeOver, devicePassNumber,
                UploadStatus, UploadResult, UploadTime,
                CreateTime, LastUpdatetime);


            await UpdateDeviceAccessTotal(hsDeviceID);
        }

        public void Reupload(List<int> accessIDs)
        {
            var sSQL = @"Update  [FacePeopleAccessDetail]  set UploadResult=0,UploadStatus=0,LastUpdatetime={0}  
                        Where AccessID in (%ID%) and UploadStatus=1;";
            sSQL = sSQL.Replace("%ID%", string.Join(",", accessIDs));
            Excute(sSQL, DateTime.Now);

            var doors = Query<FaceDeviceDetail>(null).AsNoTracking().Select(d => d.ID);
            // UpdateDeviceAccessTotal(doors).Wait();
            UpdateDeviceAccessTotal1(doors);
        }

        public void ReuploadAll()
        {
            var sSQL = "Update  [FacePeopleAccessDetail]  set UploadResult=0,UploadStatus=0,LastUpdatetime={0}  Where UploadStatus=1;";
            Excute(sSQL, DateTime.Now);

            //更新缓存
            var doors = Query<FaceDeviceDetail>(null).AsNoTracking().Select(d => d.ID);
            // UpdateDeviceAccessTotal(doors).Wait();
            UpdateDeviceAccessTotal1(doors);
        }

        public void Delete(string sn, List<uint> codes)
        {
            var device = _Cache.GetDevice(sn);
            var personList = Set<FacePeopleInfo>().Where(a => codes.Contains(a.Code)).ToList();
            var accessTb = Set<FacePeopleAccessDetail>();
            foreach (var item in personList)
            {
                var access = accessTb.FirstOrDefault(a => a.DeviceID == device.Id && a.PeopleID == item.Id);
                if (access != null)
                    base.Delete(access);
            }
        }
    }
}
